
--
-- Drop taMemberCopy and MemberChanges and MemberErrors (if exists)
--
--    This ensures this sql file can always be
--    be executed even if the previous iteration
--    failed.
--
drop table taMemberCopy;
drop table MemberChanges;
drop table MemberErrors;

--
-- Create taMemberCopy taking a copy of all data
--
--    Taking allows us to work with the copy instead
--    of the actual data, allowing other people to
--    use the database while this script is running.
--
create table taMemberCopy
  as 
    (
      select *
        from taMember
    )
;

--
-- Create taMemberOld as empty table if it does not exist
--
--    This ensures that we can use the same logic
--    to take changes in the following step.
--
create table taMemberOld
  as
    (
      select *
        from taMemberCopy
        where 1=0
    )
;

--
-- Create the MemberChanges Table
--
--    Create a table containing all the changes
--    to taMember, including a new column named
--    operation as either 'added', 'changed' or
--    'deleted' indicating what change happend.
--
create table MemberChanges as
select  'added' as operation
      , m.memberno
      , m.initials
      , m.name
      , m.address
      , m.zipcode
      , m.dateborn
      , m.datejoined
      , m.dateleft
      , m.ownsplanereg
      , m.statusstudent
      , m.statuspilot
      , m.statusascat
      , m.statusfullcat
      , m.sex
      , m.club
  from taMemberCopy m
  where exists (
                  select 'x'
                    from ( select  new.memberno
                              from taMemberCopy new
                          minus
                           select  old.memberno
                              from taMemberOld old
                          ) added
                    where m.memberno = added.memberno
                )
union
select  'changed' as operation
      , memberno
      , initials
      , name
      , address
      , zipcode
      , dateborn
      , datejoined
      , dateleft
      , ownsplanereg
      , statusstudent
      , statuspilot
      , statusascat
      , statusfullcat
      , sex
      , club
  from  (
           select mc.memberno
                , mc.initials
                , mc.name
                , mc.address
                , mc.zipcode
                , mc.dateborn
                , mc.datejoined
                , mc.dateleft
                , mc.ownsplanereg
                , mc.statusstudent
                , mc.statuspilot
                , mc.statusascat
                , mc.statusfullcat
                , mc.sex
                , mc.club
             from taMemberCopy mc
           minus
          select  mo.memberno
                , mo.initials
                , mo.name
                , mo.address
                , mo.zipcode
                , mo.dateborn
                , mo.datejoined
                , mo.dateleft
                , mo.ownsplanereg
                , mo.statusstudent
                , mo.statuspilot
                , mo.statusascat
                , mo.statusfullcat
                , mo.sex
                , mo.club
               from  taMemberOld mo
          ) changed
  where not exists (
                  select 'x'
                    from ( select  new.memberno
                              from taMemberCopy new
                          minus
                           select  old.memberno
                              from taMemberOld old
                          ) added
                    where changed.memberno = added.memberno
                )
union
select  'deleted' as operation
      , m.memberno
      , m.initials
      , m.name
      , m.address
      , m.zipcode
      , m.dateborn
      , m.datejoined
      , m.dateleft
      , m.ownsplanereg
      , m.statusstudent
      , m.statuspilot
      , m.statusascat
      , m.statusfullcat
      , m.sex
      , m.club
  from  taMemberCopy m
  where exists (
                  select 'x'
                    from ( select  old.memberno
                              from taMemberOld old
                          minus
                           select  new.memberno
                              from taMemberCopy new
                          ) deleted
                    where m.memberno = deleted.memberno
                )
;

--
--  Create error table to store any errors that may occur
--
create table MemberErrors
  as
    (
      select 'This column should contain an error message, beware!' as reason
            , m.memberno
            , m.initials
            , m.name
            , m.address
            , m.zipcode
            , m.dateborn
            , m.datejoined
            , m.dateleft
            , m.ownsplanereg
            , m.statusstudent
            , m.statuspilot
            , m.statusascat
            , m.statusfullcat
            , m.sex
            , m.club
        from taMemberCopy m
        where 1 = 0
    )
;

create or replace function QualityAssure (
    row_to_qa in MemberChanges%rowtype
  , row_to_add out Dim_Member%rowtype
)
return varchar2(256)
is
declare
  --
  -- Exceptions
  --
  invalid_status exception;
  invalid_club exception;
  invalid_dates exception;
  invalid_glider exception;
  invalid_age exception;
  
  true_status_count number(3,0) := 0;
  club_count number(3,0) := 0;
  glider_count number(3,0) := 0;
  minimum_age constant interval year To month := interval '15-0' year to month;
  rest_of_name varchar2(256) := null;
  name_unit varchar2(64) := null;
  name_split_index number(5,0) := 0;
  rest_of_name_length number(5,0) := 0;
  first_name varchar2(64) := null;
  last_name varchar2(64) := null;
  middle_names varchar2(64) := null;
begin
      --
      -- Check all four status' to ensure ONLY ONE is set to true.
      -- Also set variable for insertion in dimension.
      --
      if row_to_qa.statusstudent = 'y' or row_to_qa.statusstudent = 'Y' then
        true_status_count := true_status_count + 1;
        row_to_add.status := 'Student';
      end if;
      if row_to_qa.statuspilot = 'y' or row_to_qa.statuspilot = 'Y' then
        true_status_count := true_status_count + 1;
        row_to_add.status := 'Pilot';
      end if;
      if row_to_qa.statusascat = 'y' or row_to_qa.statusascat = 'Y' then
        true_status_count := true_status_count + 1;
        row_to_add.status := 'Assistent ...';
      end if;
      if row_to_qa.statusfullcat = 'y' or row_to_qa.statusfullcat = 'Y' then
        true_status_count := true_status_count + 1;
        row_to_add.status := 'Full ...';
      end if;
      if true_status_count > 1 then
        raise invalid_status;
      end if;
      
      -- 
      -- Check that ensures the glider exists!
      --
      row_to_add.gliderregistration := regexp_replace(row_to_qa.ownsplanereg, '[[:space:]]*',''); 
      if row_to_add.gliderregistration != '' then
      
        --
        -- Make sure it starts with OY
        --
        if row_to_add.gliderregistration not like 'OY%' then
          row_to_add.gliderregistration := 'OY' || row_to_add.gliderregistration;
        end if;
        
        -- 
        -- Check that ensures the glider exists!
        --    (THIS MUST BE EXECUTED AFTER DIM_GLIDER HAS BEEN CREATED)
        --    (taGlider SHOULD BE CHANGED TO DIM_GLIDER)
        --
        select count(*)
          into glider_count
          from taGlider
         where registration = row_to_add.gliderregistration;
        if club_count != 1 then
          raise invalid_glider;
        end if;
      end if;
      
      -- 
      -- Check that ensures the dates are proper
      --    (DateBorn, DateJoirned, DateLeft)
      --
      if (row_to_qa.dateborn + minimum_age) >= row_to_qa.datejoined then
        raise invalid_dates;
      end if;
      if row_to_qa.dateleft is not null and row_to_qa.datejoined > row_to_qa.dateleft then
        raise invalid_dates;
      end if;
      
      -- 
      -- Check that ensures the club exists!
      --    (THIS MUST BE EXECUTED AFTER DIM_CLUB HAS BEEN CREATED)
      --    (taClub SHOULD BE CHANGED TO DIM_CLUB)
      --
      select count(*)
        into club_count
        from taClub
        where mane = row_to_qa.club;
      if club_count != 1 then
        raise invalid_club;
      end if;
      
      --
      -- Check that age is valid
      --
      if trunc(months_between(sysdate, row_to_qa.dateborn) / 12) > 150 then
        raise invalid_age;
      end if;
      
      --
      -- Change age to either 'Male' or 'Female'
      --    This check should not cause a problem since
      --    this column is restricted to only 'm' or 'f'.
      --
      if row_to_qa.sex = 'm' or row_to_qa.statusstudent = 'M' then
        row_to_add.sex := 'Male';
      else
        row_to_add.sex := 'Female';
      end if;
      
      --
      -- Split name into components
      --    This ensures we get our name into components.
      --
      rest_of_name := row_to_qa.name;
      rest_of_name := trim(rest_of_name);
      rest_of_name_length := length(rest_of_name);
      
      while rest_of_name_length > 0 loop
        --
        -- find split index
        --
        name_split_index := instrc(rest_of_name, ' ');
        if name_split_index = 0 then
          name_split_index := length(rest_of_name);
        end if;
        
        --
        -- Find next unit of a name
        --
        name_unit := substrc(rest_of_name, 0, name_split_index);
        name_unit := trim(name_unit);
        
        --
        -- Update rest_of_name and rest_of_name_length
        --
        rest_of_name := substrc(rest_of_name, name_split_index + 1);
        rest_of_name := trim(rest_of_name);
        rest_of_name_length := length(rest_of_name);
        
        --
        -- Update name components
        --
        if first_name is null then
          first_name := name_unit;
        elsif rest_of_name_length = 0 then
          last_name := name_unit;
        else
          if middle_names is null then
            middle_names := name_unit;
          else
            middle_names := middle_names || ' ' || name_unit;
          end if;
        end if;
      end loop;
      row_to_add.firstname := first_name;
      row_to_add.middlename := middle_names;
      row_to_add.lastname := last_name;
      
      --
      -- All QA complete, current row green-lit.
      -- Set rest of members!
      --
      row_to_add.memberid := member_seq.nextval;
      row_to_add.memberno := row_to_qa.memberno;
      row_to_add.initials := row_to_qa.initials;
      row_to_add.fullname := row_to_qa.name;
      row_to_add.address := row_to_qa.address;
      row_to_add.zipcode := row_to_qa.zipcode;
      row_to_add.age := trunc(months_between(sysdate, row_to_qa.dateborn) / 12);
      row_to_add.datejoined := row_to_qa.datejoined;
      row_to_add.dateleft := row_to_qa.dateleft;
      row_to_add.club := row_to_qa.club;
        
      return null;
    exception
      when invalid_status then
        insert into MemberErrors 
          (reason
          ,memberno
          ,initials
          ,name
          ,address
          ,zipcode
          ,dateborn
          ,datejoined
          ,dateleft
          ,ownsplanereg
          ,statusstudent
          ,statuspilot
          ,statusascat
          ,statusfullcat
          ,sex
          ,club) 
        values 
          ('Invalid status specified'
          ,row_to_qa.memberno
          ,row_to_qa.initials
          ,row_to_qa.name
          ,row_to_qa.address
          ,row_to_qa.zipcode
          ,row_to_qa.dateborn
          ,row_to_qa.datejoined
          ,row_to_qa.dateleft
          ,row_to_qa.ownsplanereg
          ,row_to_qa.statusstudent
          ,row_to_qa.statuspilot
          ,row_to_qa.statusascat
          ,row_to_qa.statusfullcat
          ,row_to_qa.sex
          ,row_to_qa.club);
        return 'Invalid status specified';
      when invalid_club then
        insert into MemberErrors 
          (reason
          ,memberno
          ,initials
          ,name
          ,address
          ,zipcode
          ,dateborn
          ,datejoined
          ,dateleft
          ,ownsplanereg
          ,statusstudent
          ,statuspilot
          ,statusascat
          ,statusfullcat
          ,sex
          ,club) 
        values 
          ('Club does not exist'
          ,row_to_qa.memberno
          ,row_to_qa.initials
          ,row_to_qa.name
          ,row_to_qa.address
          ,row_to_qa.zipcode
          ,row_to_qa.dateborn
          ,row_to_qa.datejoined
          ,row_to_qa.dateleft
          ,row_to_qa.ownsplanereg
          ,row_to_qa.statusstudent
          ,row_to_qa.statuspilot
          ,row_to_qa.statusascat
          ,row_to_qa.statusfullcat
          ,row_to_qa.sex
          ,row_to_qa.club);
        return 'Club does not exist';
      when invalid_dates then
        insert into MemberErrors 
          (reason
          ,memberno
          ,initials
          ,name
          ,address
          ,zipcode
          ,dateborn
          ,datejoined
          ,dateleft
          ,ownsplanereg
          ,statusstudent
          ,statuspilot
          ,statusascat
          ,statusfullcat
          ,sex
          ,club) 
        values 
          ('Dates specified are not valid'
          ,row_to_qa.memberno
          ,row_to_qa.initials
          ,row_to_qa.name
          ,row_to_qa.address
          ,row_to_qa.zipcode
          ,row_to_qa.dateborn
          ,row_to_qa.datejoined
          ,row_to_qa.dateleft
          ,row_to_qa.ownsplanereg
          ,row_to_qa.statusstudent
          ,row_to_qa.statuspilot
          ,row_to_qa.statusascat
          ,row_to_qa.statusfullcat
          ,row_to_qa.sex
          ,row_to_qa.club);
        return 'Dates specified are not valid';
      when invalid_glider then
        insert into MemberErrors 
          (reason
          ,memberno
          ,initials
          ,name
          ,address
          ,zipcode
          ,dateborn
          ,datejoined
          ,dateleft
          ,ownsplanereg
          ,statusstudent
          ,statuspilot
          ,statusascat
          ,statusfullcat
          ,sex
          ,club) 
        values 
          ('Glider with specified registration does not exist'
          ,row_to_qa.memberno
          ,row_to_qa.initials
          ,row_to_qa.name
          ,row_to_qa.address
          ,row_to_qa.zipcode
          ,row_to_qa.dateborn
          ,row_to_qa.datejoined
          ,row_to_qa.dateleft
          ,row_to_qa.ownsplanereg
          ,row_to_qa.statusstudent
          ,row_to_qa.statuspilot
          ,row_to_qa.statusascat
          ,row_to_qa.statusfullcat
          ,row_to_qa.sex
          ,row_to_qa.club);
        return 'Glider with specified registration does not exist';
      when invalid_age then
        insert into MemberErrors 
          (reason
          ,memberno
          ,initials
          ,name
          ,address
          ,zipcode
          ,dateborn
          ,datejoined
          ,dateleft
          ,ownsplanereg
          ,statusstudent
          ,statuspilot
          ,statusascat
          ,statusfullcat
          ,sex
          ,club) 
        values 
          ('Too large age'
          ,row_to_qa.memberno
          ,row_to_qa.initials
          ,row_to_qa.name
          ,row_to_qa.address
          ,row_to_qa.zipcode
          ,row_to_qa.dateborn
          ,row_to_qa.datejoined
          ,row_to_qa.dateleft
          ,row_to_qa.ownsplanereg
          ,row_to_qa.statusstudent
          ,row_to_qa.statuspilot
          ,row_to_qa.statusascat
          ,row_to_qa.statusfullcat
          ,row_to_qa.sex
          ,row_to_qa.club);
        return 'Too large age';
end;
/

--
--  QA on addition from MemberChanges
--
--    This ensures no crap data is addded to the
--    dimension. This is achieved by iterating
--    through all added rows and only adding
--    rows that is not trash. All trash rows
--    are added to the previously defined
--    MemberErrors table.
--
declare
  --
  -- Cursors
  --
  cursor member_additions is
    select *
    from MemberChanges
    where operation = 'added';
    
  -- 
  -- Row to add
  --
  row_to_add Dim_Member%rowtype;
begin
  -- 
  -- Go through all additions
  --
  for row_to_qa in member_additions loop
    --
    -- Block to catch exceptions (errors in data)
    --
    declare 
      --
      -- Exceptions
      --
      invalid_status exception;
      invalid_club exception;
      invalid_dates exception;
      invalid_glider exception;
      invalid_age exception;
  
      true_status_count number(3,0) := 0;
      club_count number(3,0) := 0;
      glider_count number(3,0) := 0;
      minimum_age constant interval year To month := interval '15-0' year to month;
      rest_of_name varchar2(256) := null;
      name_unit varchar2(64) := null;
      name_split_index number(5,0) := 0;
      rest_of_name_length number(5,0) := 0;
      first_name varchar2(64) := null;
      last_name varchar2(64) := null;
      middle_names varchar2(64) := null;
      
      
      glider_reg Dim_Member.gliderregistration%type;
      status Dim_Member.status%type;
      sex Dim_Member.sex%type;
      
    begin
      --
      -- Check all four status' to ensure ONLY ONE is set to true.
      -- Also set variable for insertion in dimension.
      --
      if row_to_qa.statusstudent = 'y' or row_to_qa.statusstudent = 'Y' then
        true_status_count := true_status_count + 1;
        row_to_add.status := 'Student';
      end if;
      if row_to_qa.statuspilot = 'y' or row_to_qa.statuspilot = 'Y' then
        true_status_count := true_status_count + 1;
        row_to_add.status := 'Pilot';
      end if;
      if row_to_qa.statusascat = 'y' or row_to_qa.statusascat = 'Y' then
        true_status_count := true_status_count + 1;
        row_to_add.status := 'Assistent ...';
      end if;
      if row_to_qa.statusfullcat = 'y' or row_to_qa.statusfullcat = 'Y' then
        true_status_count := true_status_count + 1;
        row_to_add.status := 'Full ...';
      end if;
      if true_status_count > 1 then
        raise invalid_status;
      end if;
      
      -- 
      -- Check that ensures the glider exists!
      --
      row_to_add.gliderregistration := regexp_replace(row_to_qa.ownsplanereg, '[[:space:]]*',''); 
      if row_to_add.gliderregistration != '' then
      
        --
        -- Make sure it starts with OY
        --
        if row_to_add.gliderregistration not like 'OY%' then
          row_to_add.gliderregistration := 'OY' || row_to_add.gliderregistration;
        end if;
        
        -- 
        -- Check that ensures the glider exists!
        --    (THIS MUST BE EXECUTED AFTER DIM_GLIDER HAS BEEN CREATED)
        --    (taGlider SHOULD BE CHANGED TO DIM_GLIDER)
        --
        select count(*)
          into glider_count
          from taGlider
         where registration = row_to_add.gliderregistration;
        if club_count != 1 then
          raise invalid_glider;
        end if;
      end if;
      
      -- 
      -- Check that ensures the dates are proper
      --    (DateBorn, DateJoirned, DateLeft)
      --
      if (row_to_qa.dateborn + minimum_age) >= row_to_qa.datejoined then
        raise invalid_dates;
      end if;
      if row_to_qa.dateleft is not null and row_to_qa.datejoined > row_to_qa.dateleft then
        raise invalid_dates;
      end if;
      
      -- 
      -- Check that ensures the club exists!
      --    (THIS MUST BE EXECUTED AFTER DIM_CLUB HAS BEEN CREATED)
      --    (taClub SHOULD BE CHANGED TO DIM_CLUB)
      --
      select count(*)
        into club_count
        from taClub
        where mane = row_to_qa.club;
      if club_count != 1 then
        raise invalid_club;
      end if;
      
      --
      -- Check that age is valid
      --
      if trunc(months_between(sysdate, row_to_qa.dateborn) / 12) > 150 then
        raise invalid_age;
      end if;
      
      --
      -- Change age to either 'Male' or 'Female'
      --    This check should not cause a problem since
      --    this column is restricted to only 'm' or 'f'.
      --
      if row_to_qa.sex = 'm' or row_to_qa.statusstudent = 'M' then
        row_to_add.sex := 'Male';
      else
        row_to_add.sex := 'Female';
      end if;
      
      --
      -- Split name into components
      --    This ensures we get our name into components.
      --
      rest_of_name := row_to_qa.name;
      rest_of_name := trim(rest_of_name);
      rest_of_name_length := length(rest_of_name);
      
      while rest_of_name_length > 0 loop
        --
        -- find split index
        --
        name_split_index := instrc(rest_of_name, ' ');
        if name_split_index = 0 then
          name_split_index := length(rest_of_name);
        end if;
        
        --
        -- Find next unit of a name
        --
        name_unit := substrc(rest_of_name, 0, name_split_index);
        name_unit := trim(name_unit);
        
        --
        -- Update rest_of_name and rest_of_name_length
        --
        rest_of_name := substrc(rest_of_name, name_split_index + 1);
        rest_of_name := trim(rest_of_name);
        rest_of_name_length := length(rest_of_name);
        
        --
        -- Update name components
        --
        if first_name is null then
          first_name := name_unit;
        elsif rest_of_name_length = 0 then
          last_name := name_unit;
        else
          if middle_names is null then
            middle_names := name_unit;
          else
            middle_names := middle_names || ' ' || name_unit;
          end if;
        end if;
      end loop;
      row_to_add.firstname := first_name;
      row_to_add.middlename := middle_names;
      row_to_add.lastname := last_name;
      
      --
      -- All QA complete, current row green-lit to be added
      --
      insert into Dim_Member
        (
            MemberId
          , MemberNo
          , Sex
          , Initials
          , FirstName
          , MiddleName
          , LastName
          , FullName
          , Address
          , ZipCode
          , Age
          , DateJoined
          , DateLeft
          , GliderRegistration
          , ClubName
          , Status
          , ValidFrom
          , ValidTo
        )
      values
        (
            member_seq.nextval
          , row_to_qa.memberno
          , row_to_add.sex
          , row_to_qa.initials
          , row_to_add.firstname
          , row_to_add.middlename
          , row_to_add.lastname
          , row_to_qa.name
          , row_to_qa.address
          , row_to_qa.zipcode
          , trunc(months_between(sysdate, row_to_qa.dateborn) / 12)
          , row_to_qa.datejoined
          , row_to_qa.dateleft
          , row_to_add.gliderregistration
          , row_to_qa.club
          , row_to_add.status
          , trunc(sysdate,'DDD')
          , to_date('9999-12-31 23:59:59','YYYY-MM-DD HH24:MI:SS')
        );
      
    exception
      when invalid_status then
        insert into MemberErrors 
          (reason
          ,memberno
          ,initials
          ,name
          ,address
          ,zipcode
          ,dateborn
          ,datejoined
          ,dateleft
          ,ownsplanereg
          ,statusstudent
          ,statuspilot
          ,statusascat
          ,statusfullcat
          ,sex
          ,club) 
        values 
          ('Invalid status specified'
          ,row_to_qa.memberno
          ,row_to_qa.initials
          ,row_to_qa.name
          ,row_to_qa.address
          ,row_to_qa.zipcode
          ,row_to_qa.dateborn
          ,row_to_qa.datejoined
          ,row_to_qa.dateleft
          ,row_to_qa.ownsplanereg
          ,row_to_qa.statusstudent
          ,row_to_qa.statuspilot
          ,row_to_qa.statusascat
          ,row_to_qa.statusfullcat
          ,row_to_qa.sex
          ,row_to_qa.club);
      when invalid_club then
        insert into MemberErrors 
          (reason
          ,memberno
          ,initials
          ,name
          ,address
          ,zipcode
          ,dateborn
          ,datejoined
          ,dateleft
          ,ownsplanereg
          ,statusstudent
          ,statuspilot
          ,statusascat
          ,statusfullcat
          ,sex
          ,club) 
        values 
          ('Club does not exist'
          ,row_to_qa.memberno
          ,row_to_qa.initials
          ,row_to_qa.name
          ,row_to_qa.address
          ,row_to_qa.zipcode
          ,row_to_qa.dateborn
          ,row_to_qa.datejoined
          ,row_to_qa.dateleft
          ,row_to_qa.ownsplanereg
          ,row_to_qa.statusstudent
          ,row_to_qa.statuspilot
          ,row_to_qa.statusascat
          ,row_to_qa.statusfullcat
          ,row_to_qa.sex
          ,row_to_qa.club);
      when invalid_dates then
        insert into MemberErrors 
          (reason
          ,memberno
          ,initials
          ,name
          ,address
          ,zipcode
          ,dateborn
          ,datejoined
          ,dateleft
          ,ownsplanereg
          ,statusstudent
          ,statuspilot
          ,statusascat
          ,statusfullcat
          ,sex
          ,club) 
        values 
          ('Dates specified are not valid'
          ,row_to_qa.memberno
          ,row_to_qa.initials
          ,row_to_qa.name
          ,row_to_qa.address
          ,row_to_qa.zipcode
          ,row_to_qa.dateborn
          ,row_to_qa.datejoined
          ,row_to_qa.dateleft
          ,row_to_qa.ownsplanereg
          ,row_to_qa.statusstudent
          ,row_to_qa.statuspilot
          ,row_to_qa.statusascat
          ,row_to_qa.statusfullcat
          ,row_to_qa.sex
          ,row_to_qa.club);
      when invalid_glider then
        insert into MemberErrors 
          (reason
          ,memberno
          ,initials
          ,name
          ,address
          ,zipcode
          ,dateborn
          ,datejoined
          ,dateleft
          ,ownsplanereg
          ,statusstudent
          ,statuspilot
          ,statusascat
          ,statusfullcat
          ,sex
          ,club) 
        values 
          ('Glider with specified registration does not exist'
          ,row_to_qa.memberno
          ,row_to_qa.initials
          ,row_to_qa.name
          ,row_to_qa.address
          ,row_to_qa.zipcode
          ,row_to_qa.dateborn
          ,row_to_qa.datejoined
          ,row_to_qa.dateleft
          ,row_to_qa.ownsplanereg
          ,row_to_qa.statusstudent
          ,row_to_qa.statuspilot
          ,row_to_qa.statusascat
          ,row_to_qa.statusfullcat
          ,row_to_qa.sex
          ,row_to_qa.club);
      when invalid_age then
        insert into MemberErrors 
          (reason
          ,memberno
          ,initials
          ,name
          ,address
          ,zipcode
          ,dateborn
          ,datejoined
          ,dateleft
          ,ownsplanereg
          ,statusstudent
          ,statuspilot
          ,statusascat
          ,statusfullcat
          ,sex
          ,club) 
        values 
          ('Too large age'
          ,row_to_qa.memberno
          ,row_to_qa.initials
          ,row_to_qa.name
          ,row_to_qa.address
          ,row_to_qa.zipcode
          ,row_to_qa.dateborn
          ,row_to_qa.datejoined
          ,row_to_qa.dateleft
          ,row_to_qa.ownsplanereg
          ,row_to_qa.statusstudent
          ,row_to_qa.statuspilot
          ,row_to_qa.statusascat
          ,row_to_qa.statusfullcat
          ,row_to_qa.sex
          ,row_to_qa.club);
    end;
  end loop;
  
  rollback;
end;
/

